USE [DSRSystem_Temp]
GO

/****** Object:  StoredProcedure [dbo].[spUpdateSources]    Script Date: 1/26/2016 12:57:07 PM ******/
DROP PROCEDURE [dbo].[spUpdateSources]
GO

/****** Object:  StoredProcedure [dbo].[spUpdateSources]    Script Date: 1/26/2016 12:57:07 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




create PROCEDURE [dbo].[spUpdateSources]
	@CompanyIds NVARCHAR(MAX)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @XML XML
	SET @XML = CONVERT(XML, @CompanyIds, 1)

	DECLARE @SelectedCompanies TABLE
	(
		Id uniqueidentifier
	)
	INSERT INTO @SelectedCompanies
	SELECT TID.id.value('.','uniqueidentifier') AS Id
	FROM @XML.nodes('/CompanyIds') AS T(c) outer apply T.c.nodes('Id') as TID(id)
	
	
	DECLARE @transaction_isolation_level SMALLINT;

	SELECT @transaction_isolation_level = transaction_isolation_level
    FROM sys.dm_exec_requests
    WHERE session_id = @@spid;

    print('spUpdateSources transaction_isolation_level: ' + STR(@transaction_isolation_level))


	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.Sources') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[Sources](
			[SourceIdId] uniqueidentifier NOT NULL,
			[CompanyId] uniqueidentifier NOT NULL,
			[SourceText] nvarchar(max) NOT NULL,
			[IndicatorsNumber] nvarchar(max) NULL
		) ON [PRIMARY]
	END
	IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.StageSources') AND type in (N'U'))
	BEGIN
		CREATE TABLE [dbo].[StageSources](
			[SourceIdId] uniqueidentifier NOT NULL,
			[CompanyId] uniqueidentifier NOT NULL,
			[SourceText] nvarchar(max) NOT NULL,
			[IndicatorsNumber] nvarchar(max) NULL
		) ON [PRIMARY]
	END

	declare @startTime datetime
	declare @endTime datetime
	declare @elapsedTime int
	declare @rowsAffected int

	select @startTime = getdate()



	TRUNCATE TABLE [dbo].[Sources]
	INSERT INTO [dbo].[Sources]
	EXECUTE [DSRSystem].[dbo].[GetNonControversySources]
		
	set @rowsAffected = @@ROWCOUNT

	select @endTime = getdate()
	select @elapsedTime = datediff(second, @startTime, @endTime)
	print 'Prepare Sources => Elapsed time:' +  str(@elapsedTime) + ', RowsAffected:' + str(@rowsAffected)
	select @startTime = getdate()



	--DELETE IndicatorTickboxes FROM dbo.IndicatorTickboxes AS IndicatorTickboxes
	--LEFT OUTER JOIN dbo.FinalizedCompanies AS FinalizedCompanies ON IndicatorTickboxes.CompanyId = FinalizedCompanies.CompanyId
	--WHERE  (FinalizedCompanies.CompanyId IS NULL)   
	

	--set @rowsAffected = @@ROWCOUNT

	--select @endTime = getdate()
	--select @elapsedTime = datediff(second, @startTime, @endTime)
	--print 'Delete Indicator Tickboxes that do not belong to current companies => Elapsed time:' +  str(@elapsedTime) + ', RowsAffected:' + str(@rowsAffected)
	--select @startTime = getdate()



	--DELETE IndicatorTickboxes FROM dbo.IndicatorTickboxes AS IndicatorTickboxes
	--INNER JOIN @SelectedCompanies AS SelectedCompanies ON IndicatorTickboxes.CompanyId = SelectedCompanies.Id

	--set @rowsAffected = @@ROWCOUNT

	--select @endTime = getdate()
	--select @elapsedTime = datediff(second, @startTime, @endTime)
	--print 'Delete Indicator Tickboxes that are going to be updated => Elapsed time:' +  str(@elapsedTime) + ', RowsAffected:' + str(@rowsAffected)
	--select @startTime = getdate()



	--INSERT INTO [dbo].[IndicatorTickboxes]
	--SELECT * FROM [dbo].[StageIndicatorTickboxes]

	--set @rowsAffected = @@ROWCOUNT

	--select @endTime = getdate()
	--select @elapsedTime = datediff(second, @startTime, @endTime)
	--print 'Indicator Tickboxes have been updated => Elapsed time:' +  str(@elapsedTime) + ', RowsAffected:' + str(@rowsAffected)

END

GO


